﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using SYH.Config;
using Common;

namespace SYH.Product.DAL
{
  public   class ProductInfo
    {
        #region 产品标题是否存在
        /// <summary>
        /// 产品标题是否存在
        /// </summary>
        /// <returns></returns>
        public bool TitleExists(int userId, string title)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4),
                    new SqlParameter("@Title", SqlDbType.NVarChar,100),
                    new SqlParameter("@Result", SqlDbType.Int,4)
			};
            parameters[0].Value = userId;
            parameters[1].Value = title;
            parameters[2].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.StoredProcedure, "Product_Info_TitleExists", parameters);
            int result = TypeParse.ObjectToInt(parameters[2].Value, 0);
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 增加产品
        /// <summary>
        /// 增加产品
        /// </summary>
        /// <param name="model"></param>
        /// <param name="oftenText">常用分类名</param>
        /// <param name="oftenValue">常用分类值</param>
        /// <param name="imgList">产品图片</param>
        /// <param name="imgList">产品图片ID</param>
        /// <returns></returns>
        public int Add(Model.ProductInfo model)//, string oftenText, string oftenValue, string imgNameList, string imgIdList
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@Type", SqlDbType.Int,4),
					new SqlParameter("@Brand", SqlDbType.NVarChar,50),
					new SqlParameter("@Model", SqlDbType.NVarChar,50),
                    new SqlParameter("@ProdType", SqlDbType.Int,4),
					new SqlParameter("@Title", SqlDbType.NVarChar,100),
					new SqlParameter("@SupplyTitle", SqlDbType.NVarChar,100),
					new SqlParameter("@CustomtypeId", SqlDbType.Int,4),
                    new SqlParameter("@ImgName", SqlDbType.VarChar,200),
					new SqlParameter("@Unit", SqlDbType.NVarChar,10),
					new SqlParameter("@Price", SqlDbType.Decimal,9),
					new SqlParameter("@SupplyCount", SqlDbType.Int,4),
					new SqlParameter("@LeastBuyCount", SqlDbType.Int,4),
                    new SqlParameter("@Validity", SqlDbType.Int,4),
                    new SqlParameter("@ViewCount", SqlDbType.Int,4),
                    new SqlParameter("@Summary", SqlDbType.NVarChar,255),
                    new SqlParameter("@Content", SqlDbType.NVarChar),//此为ProductContent表
                    //new SqlParameter("@OftenText", SqlDbType.NVarChar,50),
                   // new SqlParameter("@OftenValue", SqlDbType.VarChar,50),
                   // new SqlParameter("@ImgNameList", SqlDbType.VarChar,200),
                  //  new SqlParameter("@imgIdList", SqlDbType.VarChar,200),
                    new SqlParameter("@IndustryIdLv1",SqlDbType.Int ,4),
                    new SqlParameter("@IndustryIdLv2",SqlDbType.Int ,4),
                    new SqlParameter("@IndustryIdLv3",SqlDbType.Int ,4),
                    new SqlParameter("@Images", SqlDbType.VarChar,1000),
                    new SqlParameter("@flag", SqlDbType.Int,4), 
                    new SqlParameter("@Status", SqlDbType.Int,4), 
                    new SqlParameter("@Id", SqlDbType.Int,4),
                    new SqlParameter("@AreaIdLv1",SqlDbType.Int ,4),
                    new SqlParameter("@AreaIdLv2",SqlDbType.Int ,4),
                    new SqlParameter("@AreaIdLv3",SqlDbType.Int ,4),
                    new SqlParameter("@ActivityPrice",SqlDbType.Decimal ,9)
                    };

            parameters[0].Value = model.UserId;
            parameters[1].Value = model.Type;
            parameters[2].Value = model.Brand;
            parameters[3].Value = model.Model;
            parameters[4].Value = model.ProductType;
            parameters[5].Value = model.Title;
            parameters[6].Value = model.SupplyTitle;
            parameters[7].Value = model.CustomtypeId;
            parameters[8].Value = model.ImgName;
            parameters[9].Value = model.Unit;
            parameters[10].Value = model.Price;
            parameters[11].Value = model.SupplyCount;
            parameters[12].Value = model.LeastBuyCount;
            parameters[13].Value = model.Validity;
            parameters[14].Value = model.ViewCount;
            parameters[15].Value = model.Summary;
            parameters[16].Value = model.ProductContent.Content;
            parameters[17].Value = model.IndustryIdLv1;//oftenText;
            parameters[18].Value = model.IndustryIdLv2;//oftenValue;
            parameters[19].Value = model.IndustryIdLv3;// imgNameList;
            parameters[20].Value = model.Images;//imgIdList;
            parameters[21].Value = model.Flag;
            parameters[22].Value = model.Status;
            parameters[23].Direction = ParameterDirection.Output;
            parameters[24].Value = model.AreaIdLv1;
            parameters[25].Value = model.AreaIdLv2;
            parameters[26].Value = model.AreaIdLv3;
            parameters[27].Value = model.ActivityPrice;
            SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.StoredProcedure, "Product_Info_Add", parameters);
            return TypeParse.ObjectToInt(parameters[23].Value, 0);
        }
        #endregion

        #region 更新产品
        /// <summary>
        /// 更新产品
        /// </summary>
        /// <param name="model"></param>
        /// <param name="oftenText">常用分类名</param>
        /// <param name="oftenValue">常用分类值</param>
        /// <param name="imgList">产品图片</param>
        /// <param name="imgList">产品图片ID</param>
        /// <returns></returns>
        public int Update(Model.ProductInfo model)//string oftenText, string oftenValue, string imgNameList, string imgIdList
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@Type", SqlDbType.Int,4),
					new SqlParameter("@Brand", SqlDbType.NVarChar,50),
					new SqlParameter("@Model", SqlDbType.NVarChar,50),
                    new SqlParameter("@ProdType", SqlDbType.Int,4),
					new SqlParameter("@Title", SqlDbType.NVarChar,100),
					new SqlParameter("@SupplyTitle", SqlDbType.NVarChar,100),
					new SqlParameter("@CustomtypeId", SqlDbType.Int,4),
                    new SqlParameter("@ImgName", SqlDbType.VarChar,100),
					new SqlParameter("@Unit", SqlDbType.NVarChar,10),
					new SqlParameter("@Price", SqlDbType.Decimal,9),
					new SqlParameter("@SupplyCount", SqlDbType.Int,4),
					new SqlParameter("@LeastBuyCount", SqlDbType.Int,4),
                    new SqlParameter("@Validity", SqlDbType.Int,4),
                    new SqlParameter("@Summary", SqlDbType.NVarChar,255),
					new SqlParameter("@Status", SqlDbType.Int,4),
                    new SqlParameter("@Content", SqlDbType.NVarChar),//此为ProductContent表
                    //new SqlParameter("@OftenText", SqlDbType.NVarChar,50),
                    //new SqlParameter("@OftenValue", SqlDbType.VarChar,50),
                    //new SqlParameter("@ImgNameList", SqlDbType.VarChar,200),
                   // new SqlParameter("@imgIdList", SqlDbType.VarChar,200),
                     new SqlParameter("@IndustryIdLv1",SqlDbType.Int ,4),
                    new SqlParameter("@IndustryIdLv2",SqlDbType.Int ,4),
                    new SqlParameter("@IndustryIdLv3",SqlDbType.Int ,4),
                    new SqlParameter("@Images", SqlDbType.VarChar,1000),
                    new SqlParameter("@AreaIdLv1",SqlDbType.Int ,4),
                    new SqlParameter("@AreaIdLv2",SqlDbType.Int ,4),
                    new SqlParameter("@AreaIdLv3",SqlDbType.Int ,4),
                    new SqlParameter("@ActivityPrice",SqlDbType.Decimal ,9)
                   };
            parameters[0].Value = model.Id;
            parameters[1].Value = model.UserId;
            parameters[2].Value = model.Type;
            parameters[3].Value = model.Brand;
            parameters[4].Value = model.Model;
            parameters[5].Value = model.ProductType;
            parameters[6].Value = model.Title;
            parameters[7].Value = model.SupplyTitle;
            parameters[8].Value = model.CustomtypeId;
            parameters[9].Value = model.ImgName;
            //parameters[9].Value = imgNameList;
            parameters[10].Value = model.Unit;
            parameters[11].Value = model.Price;
            parameters[12].Value = model.SupplyCount;
            parameters[13].Value = model.LeastBuyCount;
            parameters[14].Value = model.Validity;
            parameters[15].Value = model.Summary;
            parameters[16].Value = model.Status;
            parameters[17].Value = model.ProductContent.Content;
            parameters[18].Value = model.IndustryIdLv1;
            parameters[19].Value = model.IndustryIdLv2;
            parameters[20].Value = model.IndustryIdLv3;
            parameters[21].Value = model.Images;
            parameters[22].Value = model.AreaIdLv1;
            parameters[23].Value = model.AreaIdLv2;
            parameters[24].Value = model.AreaIdLv3;
            parameters[24].Value = model.ActivityPrice;

            object obj = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.StoredProcedure, "Product_Info_Update", parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }

        }
        #endregion

        #region 根据id获取产品供应信息标题
        /// <summary>
        /// 根据id获取产品供应信息标题 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetSupplyTitleById(int id)
        {
            string strSql = "select SupplyTitle from Product_Info where Id=@Id";
            var result = SqlHelper.ExecuteScalar(Config.WebConfig.SqlConnString, CommandType.Text, strSql, new SqlParameter("@Id", id));
            return Convert.ToString(result);
        }
        #endregion

        #region 根据用户编号得到产品数量
        /// <summary>
        /// 根据用户编号得到产品数量
        /// </summary>
        /// <param name="Uid">用户Id</param>
        /// <returns></returns>
        public int GetCountProduct(int Uid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" select count(1) from [Product_Info] where ");
            sql.AppendFormat(" Userid={0} and IsDelete=0 ", Uid);
            return int.Parse(Common.SqlHelper.ExecuteScalar(WebConfig.SqlConnString, CommandType.Text, sql.ToString()).ToString());
        }
      #endregion

        #region 根据Id获取model
        /// <summary>
        /// 得到一个产品对象实体 根据主键
        /// </summary>		
        public Model.ProductInfo GetModel(int Id)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            SqlDataReader sdr = SqlHelper.ExecuteReader(WebConfig.SqlConnString, CommandType.StoredProcedure, "Product_Info_GetModel", parameters);
            if (sdr.HasRows)
            {
                Model.ProductInfo model = new Model.ProductInfo();
                sdr.Read();
                model.Id = sdr.GetInt32(sdr.GetOrdinal("Id"));
                model.CustomtypeId = sdr.GetInt32(sdr.GetOrdinal("CustomtypeId"));

                model.Unit = sdr.GetString(sdr.GetOrdinal("Unit"));
                model.Price = sdr.GetDecimal(sdr.GetOrdinal("Price"));
                model.SupplyCount = sdr.GetInt32(sdr.GetOrdinal("SupplyCount"));
                model.LeastBuyCount = sdr.GetInt32(sdr.GetOrdinal("LeastBuyCount"));
                model.ViewCount = sdr.GetInt32(sdr.GetOrdinal("ViewCount"));
                model.IndustryIdLv1 = sdr.GetInt32(sdr.GetOrdinal("IndustryIdLv1"));//行业类别
                model.IndustryIdLv2 = sdr.GetInt32(sdr.GetOrdinal("IndustryIdLv2"));//行业类别
                model.IndustryIdLv3 = sdr.GetInt32(sdr.GetOrdinal("IndustryIdLv3"));//行业类别
                model.Status = sdr.GetInt32(sdr.GetOrdinal("Status"));
                model.IsDelete = sdr.GetInt32(sdr.GetOrdinal("IsDelete"));
                model.EndTime = sdr.GetDateTime(sdr.GetOrdinal("EndTime"));
                model.UserId = sdr.GetInt32(sdr.GetOrdinal("UserId"));
                model.UpdateTime = sdr.GetDateTime(sdr.GetOrdinal("UpdateTime"));
                model.CreateTime = sdr.GetDateTime(sdr.GetOrdinal("CreateTime"));
                model.Type = sdr.GetInt32(sdr.GetOrdinal("Type"));

                model.Brand = sdr.GetString(sdr.GetOrdinal("Brand"));

                model.Model = sdr.GetString(sdr.GetOrdinal("Model"));
                model.ProductType = sdr.GetInt32(sdr.GetOrdinal("ProductType"));
                model.Title = sdr.GetString(sdr.GetOrdinal("Title"));

                model.SupplyTitle = sdr.GetString(sdr.GetOrdinal("SupplyTitle"));
                model.Validity = sdr.GetInt32(sdr.GetOrdinal("Validity"));

                model.Summary = sdr.GetString(sdr.GetOrdinal("Summary"));
                model.ImgName = sdr.GetString(sdr.GetOrdinal("ImgName"));
                model.Images = sdr.GetString(sdr.GetOrdinal("Images"));
                model.ProductContent.Content = sdr.GetString(sdr.GetOrdinal("Content"));
                model.Flag = sdr.GetInt32(sdr.GetOrdinal("flag"));
                model.Code = sdr.GetString(sdr.GetOrdinal("Code"));
                model.ActivityPrice = sdr.GetDecimal(sdr.GetOrdinal("ActivityPrice"));
                sdr.Close();
                return model;
            }
            sdr.Close();
            return null;
        }
        #endregion

        #region 删除产品
        /// <summary>
        /// 删除产品
        /// </summary>
        public int Delete(int UserId, string idList)
        {
            string[] idArray = idList.Split(',');
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4),
                    new SqlParameter("@IdList", SqlDbType.VarChar,200),
                    new SqlParameter("@Num", SqlDbType.Int,4)
                    };

            parameters[0].Value = UserId;
            parameters[1].Value = idList;
            parameters[2].Value = idArray.Length;
            object obj = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, "Product_Info_Delete", parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }

        }
        #endregion


        #region 更新一个字段
        /// <summary>
        /// 更新一个字段
        /// </summary>
        /// <param name="name">字段名</param>
        /// <param name="value">值</param>
        /// <param name="strWhere">条件</param>
        /// <param name="flag">字符串表示：true为字符串；false为整型、bool、日期等</param>
        /// <returns></returns>
        public bool UpdateOneField(string name, string value, string strWhere, bool flag)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Product_Info set ");
            strSql.Append(" " + name + "= ");
            if (flag)
            {
                strSql.Append(" '" + value + "' ");
            }
            else
            {
                strSql.Append(" " + value + " ");
            }
            strSql.Append(" WHERE " + strWhere);

            return SqlHelper.ExecuteSql(Config.WebConfig.SqlConnString, strSql.ToString()) > 0;
        }
        #endregion

        #region 根据用户和状态获得采购商品数目
        public int GetCountByStatusUserId(int userId, int status)
        {
            string strSql = "select count(Id) from Product_Info where IsDelete=0 and UserId=" + userId;
            switch (status)
            {
                case (int)SYH.Enum.ProductStatus.审核中:
                    strSql += " and Status=" + (int)SYH.Enum.ProductStatus.审核中;
                    break;
                case (int)SYH.Enum.ProductStatus.审核未通过:
                    strSql += " and Status=" + (int)SYH.Enum.ProductStatus.审核未通过;
                    break;
                case (int)SYH.Enum.ProductStatus.已过期:
                    strSql += " and EndTime<'" + DateTime.Now + "' and Status in(" +
                                (int)SYH.Enum.ProductStatus.已过期 + " ," +
                                (int)SYH.Enum.ProductStatus.审核通过 + ")";
                    break;
                case (int)SYH.Enum.ProductStatus.审核通过:
                    strSql += " and EndTime>='" + DateTime.Now + "' and Status=" + (int)SYH.Enum.ProductStatus.审核通过;
                    break;
            }
            return
                Common.TypeParse.ObjectToInt(
                    Common.SqlHelper.ExecuteScalar(WebConfig.SqlConnString, CommandType.Text, strSql).ToString(),
                    0);
        }
        #endregion

        #region 根据用户ID获取最新发布产品编号
        public int GetProductIdByUserId(int userId)
        {
            string strSql = @"SELECT TOP 1 [Id] where UserId=@UserId and Status=1 order by Id desc";
            SqlParameter[] parameters = {
                    new SqlParameter("@UserId",SqlDbType.Int,4)    
                    };
            parameters[0].Value = userId;
            object result = Common.SqlHelper.ExecuteScalar(Config.WebConfig.SqlConnString, CommandType.Text, strSql, parameters);
            return Common.TypeParse.ObjectToInt(result, 0);
        }
        #endregion
    }
}
